RedshiftでLAGウィンドウ関数を使ってみる

こんにちは、データアナリティクス事業本部@那覇オフィスの下地です。

最近、RedshiftにてLAG ウィンドウ関数を使う機会がありました。公式サイトよりLAG ウィンドウ関数の概要を以下に引用致します。

LAG ウィンドウ関数は、パーティションの現在の行より上 (前) の指定されたオフセットの行の値を返します。

例えば製品の売上を管理するテーブルがあった場合、同一製品の前日売上数などのカラムを同一レコードとして表示して比較するなどが出来ます。

今回は、オプションの指定を含めて試してみようと思います。

1. 前準備

LAG関数を利用するにあたり、以下のような売上の一時テーブルを作成します。

create temp table tmp_sales_table
as
select '1' as id, 'サーターアンダギー' as name, '2023-01-10' as date, 10 as sales_counts
union all
select '2' as id, 'サーターアンダギー' as name, '2023-01-11' as date, 20 as sales_counts
union all
select '3' as id, 'サーターアンダギー' as name, '2023-01-12' as date, null as sales_counts
union all
select '4' as id, 'サーターアンダギー' as name, '2023-01-13' as date, 40 as sales_counts
union all
select '5' as id, '砂糖天ぷら' as name, '2023-01-10' as date, 2 as sales_counts
union all
select '6' as id, '砂糖天ぷら' as name, '2023-01-12' as date, 5 as sales_counts
union all
select '7' as id, '砂糖天ぷら' as name, '2023-01-13' as date, 10 as sales_counts
;

2. 実装

売上の一時テーブルを利用して、各商品に対して前日/2日前の売上データを取得してみたいと思います。

2.1 構文

LAG ウィンドウ関数構文は以下となります。

LAG (value_expr [, offset ])
[ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ PARTITION BY window_partition ] ORDER BY window_ordering )

引数の内容について表にまとめます。

引数	内容	備考
value_expr	関数の対象となる列または式
offset	値を返す現在の行より前の行数を指定するオプションのパラメータ	デフォルト設定は1
IGNORE NULLS	Amazon Redshift が使用する行を決定するときに null 値をスキップすることを指定するオプション
RESPECT NULLS	Amazon Redshift は使用される行を決定するために null 値を含める	デフォルト設定
OVER	ウィンドウのパーティションおよび並び順を指定
PARTITION BY	OVER 句の各グループのレコードの範囲を設定するオプションの引数
ORDER BY	各パーティション内の行をソート

2.2 実行コードと指定オプション

今回は3つのパターンを取得してみたいと思います。

1. 出力対象カラムにNull値を含む1行前のレコードの値を取得
2. 出力対象カラムにNull値を含む2行前のレコードの値を取得
3. 出力対象カラムにNull値を含まず1行前のレコードの値を取得

実行する際の引数を表にまとめます。Null値を含む場合は「RESPECT NULLS」、含まない場合は「IGNORE NULLS」になりますのでどちらか片方を選択します。

実行引数/パターン	value_expr	offset	RESPECT NULLS	IGNORE NULLS	PARTITION BY	ORDER BY
パターン1	sales_counts	1	◯		name	date
パターン2	sales_counts	2	◯		name	date
パターン3	sales_counts	1		◯	name	date

上記表を元にしたクエリになります。

select *
, LAG(sales_counts, 1) RESPECT NULLS OVER (PARTITION BY name ORDER BY date) as pattern_1
, LAG(sales_counts, 2) RESPECT NULLS OVER (PARTITION BY name ORDER BY date) as pattern_2
, LAG(sales_counts, 1) IGNORE  NULLS OVER (PARTITION BY name ORDER BY date) as pattern_3
from tmp_sales_table
order by name, date;

2.3 実行結果

コードを実行した結果の図は以下となります。 3パターンそれぞれで意図した結果が取得できたことが確認できました!

2.4 おまけ

ところで、「砂糖天ぷら」のことを沖縄方言では「サーターアンダギー」と言います。

今回作成した一時テーブルには両方が混在しておりますのでcase文を使用して名前を統一するようにコードを修正し再度実行します。

select case name when '砂糖天ぷら' then 'サーターアンダギー' else name end as rename 
, date
, SUM(sales_counts) as total_counts
, LAG(total_counts, 1) RESPECT NULLS OVER (PARTITION BY rename ORDER BY date) as pattern_1
, LAG(total_counts, 2) RESPECT NULLS OVER (PARTITION BY rename ORDER BY date) as pattern_2
, LAG(total_counts, 1) IGNORE  NULLS OVER (PARTITION BY rename ORDER BY date) as pattern_3
from tmp_sales_table
group by rename, date
order by rename, date;

先ほどは、サーターアンダギーの「2023-01-12」項目がNullになっておりましたが集計されてことにより数値が入って処理されました。

3. まとめ

LAGウィンドウ関数の実行方法についてまとめました。

オプション設定を含めて使えると、とても便利だなと実感しましたので他にサポートされているウィンドウ関数も試していきたいなと思います。

4. 参考リンク

• LAG ウィンドウ関数
• [Redshift] ウィンドウ関数：LAG、LEADを使ってスケジュールの日程間隔などを表示
• ウィンドウ関数
• [Redshift] テーブル作成には複数の手段が存在する